package Database;

import com.sun.rowset.CachedRowSetImpl;
import java.sql.*;

import Helper.*;

public class DB {
    private final String className = "oracle.jdbc.OracleDriver";
    private final String dbURL = "jdbc:oracle:thin:@localhost:1521:xe";
    protected Connection dbConnection = null;

    public CachedRowSetImpl get(String tableName){
        CachedRowSetImpl cachedRowSet = null;

        try{
            connect();

            Statement statement = dbConnection.createStatement();
            ResultSet res = statement.executeQuery("SELECT * FROM "+tableName);

            cachedRowSet = new CachedRowSetImpl();
            cachedRowSet.populate(res);
            // cache the result set.
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        return cachedRowSet;
    }

    public CachedRowSetImpl select(String tableName, int id){
        CachedRowSetImpl cachedRowSet = null;

        try{
            connect();

            PreparedStatement statement = dbConnection.prepareStatement("SELECT * FROM "+tableName+" WHERE id=?");
            statement.setInt(1, id);

            ResultSet res = statement.executeQuery();

            cachedRowSet = new CachedRowSetImpl();
            cachedRowSet.populate(res);
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        return cachedRowSet;
    }

    public CachedRowSetImpl select(String tableName, String name){
        CachedRowSetImpl cachedRowSet = null;

        try{
            connect();

            PreparedStatement statement = dbConnection.prepareStatement("SELECT * FROM "+tableName+" WHERE name LIKE ?");
            statement.setString(1, "%"+name+"%");

            ResultSet res = statement.executeQuery();

            cachedRowSet = new CachedRowSetImpl();
            cachedRowSet.populate(res);
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        return cachedRowSet;
    }

    // only provide the 1 'name' column version, others then help your self ~
    public boolean insert(String tableName, String name){
        int result = -1;

        try{
            connect();

            PreparedStatement statement = dbConnection.prepareStatement("INSERT INTO "+tableName+"(name) VALUES(?)");
            statement.setString(1,name);

            result = statement.executeUpdate();
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        if( result >= 0 ){
            return true;
        }else{
            return false;
        }
    }

    public boolean delete(String tableName, int id){
        int result = -1;

        try{
            connect();

            PreparedStatement statement = dbConnection.prepareStatement("DELETE FROM "+tableName+" WHERE id=?");
            statement.setInt(1,id);

            result = statement.executeUpdate();
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        if( result >= 0 ){
            return true;
        }else{
            return false;
        }
    }

    // only provide the 1 'name' column version, others then help your self ~
    public boolean update(String tableName, int id, String name){
        int result = -1;

        try{
            connect();

            PreparedStatement statement = dbConnection.prepareStatement("UPDATE "+tableName+" SET name=? WHERE id=?");
            statement.setString(1,name);
            statement.setInt(2,id);

            result = statement.executeUpdate();
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        if( result == 0 ){
            return true;
        }else{
            return false;
        }
    }

    protected void connect(){
        try{
            Class.forName(className);
            // set the database driver.

            dbConnection = DriverManager.getConnection(dbURL, "joe", "00001011");
            // get connected to the db file.
        }catch(ClassNotFoundException exception){
            System.err.println("数据库驱动未配置！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }
    }
    protected void close(){
        try{
            dbConnection.close();
        }catch(SQLException exception){
            System.err.println("数据库连接关闭失败！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }
    }
}
